<?php

/*
 * This file will perform the ajax for editing a position.
 * This is being used by admEditPos.php.
 * 
 * Created by: Peter Agno Jr.
 * Date created: November 28, 2011
 * 
 * In parameters: deptPosId, positionName, and secGrpIds
 * Out parameters: flag and msg about the success of editing of position
 */

//********************************************************************************************************
/*
 * Define the functions in here
 */

    // Start - Check if position has connection with any user
    function noAccountDeptPos($deptPosId) {
        $query = 
        "
            SELECT *
            FROM Account_Dept_Pos            
            WHERE deptPosId = '$deptPosId'
        ";
        
        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
        if ( mysql_fetch_array($result) == 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check if position has connection with any user 
    
    // Start - Check if $deptPosId has connection with RouteStepDeptPos
    function noRouteStepDeptPos($deptPosId) {
        $query = 
        "
            SELECT *
            FROM RouteStep_Dept_Pos            
            WHERE deptPosId = '$deptPosId'
        ";
        
        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
        if ( mysql_fetch_array($result) == 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check if $deptPosId has connection with RouteStepDeptPos
    
    // Start - Check if $deptPosId has connection with SentAttachment
    function noSentAttach($deptPosId) {
        $query = 
        "
            SELECT *
            FROM Sent_Attachment           
            WHERE receiverId = '$deptPosId' AND orgFlag = 'Internal'
        ";
        
        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
        if ( mysql_fetch_array($result) == 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check if $deptPosId has connection with SentAttachment
    
    // Start - Edit data in Position
    function editPos($positionId, $positionName, $currentDateTime) {
        $query =
            "
                UPDATE Position 
                SET positionName = '$positionName', positionLastUpdated = '$currentDateTime'
                WHERE positionId = '$positionId';
            ";

        mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
    } // End - Edit data in Position
    
    // Start - Check there is data already in Dept_Pos_SecurityGroup
    function noDataDPSG($deptPosId, $secGrpId) {
        $query = 
        "
            SELECT *
            FROM Dept_Pos_SecurityGroup         
            WHERE deptPosId = '$deptPosId' AND securityGroupId = '$secGrpId'
        ";
        
        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
        if ( mysql_fetch_array($result) == 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check there is data already in Dept_Pos_SecurityGroup
    
    // Start - Edit data in Dept_Pos_SecurityGroup
    function editSecGrp($deptPosId, $secGrpIds) {
        $secGrpIds = explode(",", $secGrpIds);
        
        // Insert data in Dept_Pos_SecurityGroup
        for ( $x = 0 ; $x < count($secGrpIds) - 1 ; $x++ ) {
            if ( noDataDPSG($deptPosId, $secGrpIds[$x]) == true ) {
                $query =
                "
                    INSERT INTO Dept_Pos_SecurityGroup (deptPosId, securityGroupId) VALUES
                        ('$deptPosId', '$secGrpIds[$x]');
                ";

                mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
            }
        }
        
        // Start - Delete data that were removed
        $secGrpQuery = 
        "
            SELECT *
            FROM Dept_Pos_SecurityGroup         
            WHERE deptPosId = '$deptPosId'
        ";
        
        $secGrpResult = mysql_query($secGrpQuery) or die ('Error in query: $secGrpQuery. ' . mysql_error());
        
        while ( $secGrpRow = mysql_fetch_array($secGrpResult) ) {
            $securityGroupId = $secGrpRow['securityGroupId'];
            
            if ( !in_array($securityGroupId, $secGrpIds) ) {
                $query =
                "
                    DELETE FROM Dept_Pos_SecurityGroup 
                    WHERE deptPosId = '$deptPosId' AND securityGroupId = '$securityGroupId';
                ";

                mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
            }
        }
    } // End - Edit data in Dept_Pos_SecurityGroup
    
//********************************************************************************************************

session_start();

// Start - Checker for those users who will just go to the page by typing directly in the url.
if ($_POST) {
    include('../includes/siteConfig.php');
    
    // connect to database
    $connect = mysql_connect($hostName,$rootName,$dBasePassword) or die ('Unable to connect!');
    mysql_select_db($dBaseName) or die ('Unable to select database!');
    
    // Get the data from post
    $deptPosId = $_POST['deptPosId'];
    $positionName = $_POST['positionName'];
    $secGrpIds = $_POST['secGrpIds'];
    
    // Get deptPosId other details
    $query =
        "
            SELECT D.departmentName as departmentName, P.positionId as positionId, P.positionName as positionName
            FROM Department_Position as DP, Department as D, Position as P
            WHERE DP.deptPosId = '$deptPosId' AND DP.departmentId = D.departmentId AND DP.positionId = P.positionId
        ";

    $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
    $row = mysql_fetch_array($result);
    $positionId = $row['positionId'];
    
    //GET THE CURRENT DATE TIME
    date_default_timezone_set('Asia/Singapore');
    $currentDateTime = date("Y-m-d H:i:s" , time());
    
    if ( noAccountDeptPos($deptPosId) == true && noRouteStepDeptPos($deptPosId) == true && noSentAttach($deptPosId) == true ) {
        // Can be edited or deleted if no data in:
        // * No data in Account_Dept_Pos
        // * No data in RouteStep_Dept_Pos
        // * No data in Sent_Attachment
        editPos($positionId, $positionName, $currentDateTime);
        editSecGrp($deptPosId, $secGrpIds);

        $jsondata = array();
        $jsondata['msg'] = "Position name of ".$positionId." was successfully updated.";
        $jsondata['flag'] = "success";
        $feed[] = $jsondata;
        echo json_encode($feed);
    }
    else {
        $jsondata = array();
        $jsondata['msg'] = "Error : Ajax in editing the position was not successful.";
        $jsondata['flag'] = "error";
        $feed[] = $jsondata;
        echo json_encode($feed);
    }
    
    mysql_close($connect);
}   // End - Checker for those users who will just go to the page by typing directly in the url.
else {
    echo "You are not authorized to view this page. This incident will be reported immediately.";
}
?>
